<?php

namespace nabu\models\blogModel;

defined('NABU') || exit;

require_once 'database/database.php';

use PDOException, nabu\database\database\database;

class blogModel extends database {
  public function __construct() {
    parent::__construct();
  }

  // @return el 'id' de un artículo dado su 'URL'.
  public function article_id(string $slug) {
    $query = 'SELECT id FROM articles WHERE slug = ? LIMIT 1';

    try {
      $result = $this -> pdo -> prepare($query);

      $result -> execute(array($slug));

      $article = $result -> fetch();

      if ($article)
        return $article['id'];

      return $article;
    }
    catch(PDOException $e) {
      $this -> errors($e -> getMessage(), 'tuvimos un problema para buscar el artículo');
    }
  }

  // Registra un artículo en espera de aprobación.
  public function save_article(array $data) {
    $query = 'INSERT INTO articles(user_id, title, synopsis, content, slug, creation_date) ' .
             'VALUES(:user_id, :title, :synopsis, :content, :slug, :creation_date)';

    $data['creation_date'] = $this -> current_date();

    try {
      $this -> pdo -> prepare($query) -> execute($data);
    }
    catch(PDOException $e) {
      $this -> errors($e -> getMessage(), 'tuvimos un problema para registrar tu artículo');
    }
  }

  // @return una lista de artículos no publicados dado el 'id' del usuario.
  public function sent_articles(int $id) {
    $query = 'SELECT title, synopsis FROM articles WHERE user_id = ? AND authorized = FALSE ORDER BY creation_date DESC';

    try {
      $result = $this -> pdo -> prepare($query);

      $result -> execute(array($id));

      return $result -> fetchAll();
    }
    catch(PDOException $e) {
      $this -> errors($e -> getMessage(), 'tuvimos un problema para buscar tus artículos en espera de aprobación');
    }
  }

  // @return el 'número total de artículos autorizados'.
  public function count_articles() {
    $query = 'SELECT COUNT(*) AS total FROM articles WHERE authorized = TRUE';

    try {
      $result = $this -> pdo -> prepare($query);

      $result -> execute();

      $total = $result -> fetch();

      if ($total)
        return $total['total'];

      return $total;
    }
    catch(PDOException $e) {
      $this -> errors($e -> getMessage(), 'tuvimos un problema para contar el número total de artículos publicados');
    }
  }

  // @return un array de 'todos los artículos autorizados'.
  public function all_articles(int $num_results, int $accumulation) {
    $query = 'SELECT a.title, a.synopsis, a.slug, a.cover, u.username AS author, COUNT(c.article_id) AS comments, COUNT(f.article_id) AS favorites ' .
             'FROM articles AS a ' .
             'INNER JOIN users AS u ON a.user_id = u.id ' .
             'LEFT JOIN comments AS c ON a.id = c.article_id ' .
             'LEFT JOIN favorites AS f ON a.id = f.article_id ' .
             'WHERE a.authorized = TRUE GROUP BY a.id ORDER BY a.title ASC LIMIT ? OFFSET ?';

    try {
      $result = $this -> pdo -> prepare($query);

      $result -> execute(array($num_results, $accumulation));

      return $result -> fetchAll();
    }
    catch(PDOException $e) {
      $this -> errors($e -> getMessage(), 'tuvimos un problema para mostrar todos los artículos');
    }
  }

  // @return el 'número total de artículos por patrón de búsqueda'.
  public function count_search(string $pattern) {
    $query = 'SELECT COUNT(*) AS total FROM articles WHERE authorized = TRUE AND title LIKE ?';

    try {
      $result = $this -> pdo -> prepare($query);

      $result -> execute(array('%' . $pattern . '%'));

      $total = $result -> fetch();

      if ($total)
        return $total['total'];

      return $total;
    }
    catch(PDOException $e) {
      $this -> errors($e -> getMessage(), 'tuvimos un problema para contar el número total de artículos buscados');
    }
  }

  // @return un array de artículos 'por patrón de búsqueda'.
  public function search_articles(string $pattern, int $num_results, int $accumulation) {
    $query = 'SELECT a.title, a.synopsis, a.slug, a.cover, u.username AS author, COUNT(c.article_id) AS comments, COUNT(f.article_id) AS favorites ' .
             'FROM articles AS a ' .
             'INNER JOIN users AS u ON a.user_id = u.id ' .
             'LEFT JOIN comments AS c ON a.id = c.article_id ' .
             'LEFT JOIN favorites AS f ON a.id = f.article_id ' .
             'WHERE a.authorized = TRUE AND a.title LIKE ? GROUP BY a.id ORDER BY a.title ASC LIMIT ? OFFSET ?';

    try {
      $result = $this -> pdo -> prepare($query);

      $result -> execute(array('%' . $pattern . '%', $num_results, $accumulation));

      return $result -> fetchAll();
    }
    catch(PDOException $e) {
      $this -> errors($e -> getMessage(), 'tuvimos un problema para realizar la búsqueda de artículos');
    }
  }

  // @return un array de artículos 'más valorados'.
  public function popular_articles(int $limit) {
    $query = 'SELECT a.title, a.synopsis, a.slug, a.cover, u.username AS author, COUNT(c.article_id) AS comments, COUNT(f.article_id) AS favorites ' .
             'FROM articles AS a ' .
             'INNER JOIN users AS u ON a.user_id = u.id ' .
             'LEFT JOIN comments AS c ON a.id = c.article_id ' .
             'LEFT JOIN favorites AS f ON a.id = f.article_id ' .
             'WHERE a.authorized = TRUE GROUP BY a.id ORDER BY comments, favorites DESC LIMIT ?';

    try {
      $result = $this -> pdo -> prepare($query);

      $result -> execute(array($limit));

      return $result -> fetchAll();
    }
    catch(PDOException $e) {
      $this -> errors($e -> getMessage(), 'tuvimos un problema para mostrar los artículos más valorados');
    }
  }

  // @return un array de artículos 'más recientes'.
  public function recent_articles(int $limit) {
    $query = 'SELECT a.title, a.synopsis, a.slug, a.cover, u.username AS author, COUNT(c.article_id) AS comments, COUNT(f.article_id) AS favorites ' .
             'FROM articles AS a ' .
             'INNER JOIN users AS u ON a.user_id = u.id ' .
             'LEFT JOIN comments AS c ON a.id = c.article_id ' .
             'LEFT JOIN favorites AS f ON a.id = f.article_id ' .
             'WHERE a.authorized = TRUE GROUP BY a.id ORDER BY a.modification_date DESC LIMIT ?';
    try {
      $result = $this -> pdo -> prepare($query);

      $result -> execute(array($limit));

      return $result -> fetchAll();
    }
    catch(PDOException $e) {
      $this -> errors($e -> getMessage(), 'tuvimos un problema para mostrar los artículos más recientes');
    }
  }

  // @return el 'contenido de un artículo' dado su URL.
  public function get_article(string $slug) {
    $query = 'SELECT a.title, a.content, a.slug, a.cover, a.modification_date AS date, u.name AS author, ' .
             'u.username AS author_username, p.avatar AS author_avatar, p.description AS author_description ' .
             'FROM articles AS a ' .
             'INNER JOIN users AS u ON a.user_id = u.id ' .
             'LEFT JOIN profiles AS p ON u.id = p.id ' .
             'WHERE a.authorized = TRUE AND a.slug = ? LIMIT 1';

    try {
      $result = $this -> pdo -> prepare($query);

      $result -> execute(array($slug));

      return $result -> fetch();
    }
    catch (PDOException $e) {
      $this -> errors($e -> getMessage(), 'tuvimos un problema para mostrar el contenido del artículo');
    }
  }

  // @return los 'artículos publicados' de un usuario.

  public function __destruct() {
    parent::__destruct();
  }
}
